*     *****************************************************************               * 
*     *****************************************************************               * 
*       File-Name:      get*GERDataset.do                                             *  
*       Date:           1 June 2016                                                   * 
*       Author:         Gschwend                                                      * 
*       Purpose:      	get aggregated data for Germany					              *
* 	    Input Files:    SURV_SPA_LowerSaxony_01_12_2013_SurveyData_cleaned.dta        * 
*						SURV_SPA_Bavaria_SurveyData_cleaned.dta			              *
* 						SURV_SPA_NAT_LowerSaxony_12_19_2012_SurveyData_cleaned.dta    *
* 						SURV_SPA_NAT_Bavaria_12_19_2012_SurveyData_cleaned.dta        * 
*						SURV_SPA_LowerSaxony_europe_09_22_2014_SurveyData_cleaned.dta *
*						SURV_SPA_NAT_Bavaria_12_19_2012_SurveyData_cleaned.dta        *
*       Data Output:    aggregated_GER.dta                                            *              
*     ****************************************************************                * 
*     ****************************************************************                * 

version 14.2
clear
capture log close
set more off



**********************************************
* Specify your own local path to the dropbox *
**********************************************


* Thomas G.'s local (office) path:
* local path /Users/gschwend/Dropbox/Andre/OUP multilevel electoral behavior book/chapter 6/replication/


* Thomas G.'s local (Air) path:
* local path /Users/thomasgschwend/Dropbox/Andre/OUP multilevel electoral behavior book/chapter 6/replication/
 

 

*** Save variables for LowerSaxony at the Regional level

use "SURV_GER_LowerSaxony_03_27_2013_SurveyData_cleaned.dta", clear


gen eid = 201
gen COUNTRY = "Germany"
gen LEVEL = "Regional"
gen ELECID = "LowerSaxony"

* get vote intention 8also of those who have already voted)
gen  Q8A = Q7B
replace Q8A = Q6B if Q8A==.
recode Q8A 80/max=.

gen importance_regional = Q34A
gen importance_national = Q34B
gen importance_european = Q34C

gen Q17_party1 = Q17A  
gen Q17_party2 = Q17B
gen Q17_party3 = Q17C  
gen Q17_party4 = Q17D 
gen Q17_party5 = Q17E
gen Q17_party6 = Q17F  


fre Q7B Q47* AGE female
recode Q47A 80/max=.
recode Q47B 9=.

ren Q47A pid
ren Q47B spid

gen polknow1 = (Q10A==1)
gen polknow2 = (Q10B==3)
gen polknow3 = (Q10C==5)
gen polknow4 = (Q10D==7)
gen polknow5 = (Q10E==9)
gen polknow6 = (Q10F==11)
alpha polknow1  polknow2 polknow3 polknow4 polknow5 polknow6, detail item gen(polknow)


keep eid COUNTRY LEVEL ELECID  Q8A Q17_party* importance_* pid spid polknow AGE female
log using Parties_GER.log, replace
*GERLowerSaxReg
fre Q8A Q17*
log off
compress
save "GERLowerSaxReg.dta", replace




*** Save variables for LowerSaxony at the national level

use "SURV_GER_NAT_LowerSaxony_11_27_2013_cleaned", clear


gen eid = 211
gen COUNTRY = "Germany"
gen LEVEL = "National"
gen ELECID = "LowerSaxony"

gen  Q8A = Q7B
replace Q8A = Q6B if Q8A==.
recode Q8A 80/max=.


gen importance_regional = Q34A
gen importance_national = Q34B
gen importance_european = Q34C

gen Q17_party1 = Q17A  
gen Q17_party2 = Q17B
gen Q17_party3 = Q17C  
gen Q17_party4 = Q17D 
gen Q17_party5 = Q17E
gen Q17_party6 = Q17F  
gen Q17_party7 = Q17G 
gen Q17_party8 = Q17H

fre Q7B Q47* AGE female
recode Q47A 80/max=.
recode Q47B 9=.

ren Q47A pid
ren Q47B spid

gen polknow1 = (Q10A==5)
gen polknow2 = (Q10B==7)
gen polknow3 = (Q10C==9)
gen polknow4 = (Q10D==1)
gen polknow5 = (Q10E==3)
alpha polknow1  polknow2 polknow3 polknow4 polknow5, detail item gen(polknow)

keep eid COUNTRY LEVEL ELECID  Q8A Q17_party* importance_* pid spid polknow AGE female
log on
* GERLowerSaxNat
fre Q8A Q17*
log off
compress
save "GERLowerSaxNat.dta", replace




*** Save variables for LowerSaxony at the european level

use "LowerSaxony_europe_cleaned_data", clear

gen eid = 231
gen COUNTRY = "Germany"
gen LEVEL = "European"
gen ELECID = "LowerSaxony"

fre Q8A Q6A1 Q47A

replace Q8A = Q6A1 if Q8A==.
recode Q8A 80/max=.

fre Q34*
gen importance_regional = Q34A
gen importance_national = Q34B
gen importance_european = Q34C

fre  Q47* AGE 
recode Q47A 80/max=.
recode Q47B 9=.


ren Q47A pid
ren Q47B spid


recode GEND (2 = 1) (1 = 0)
label define GEND 1 "female" 0 "male", modify
rename GEND female

gen Q17_party1 = Q17A  
gen Q17_party2 = Q17B
gen Q17_party3 = Q17C  
gen Q17_party4 = Q17D 
gen Q17_party5 = Q17E
gen Q17_party6 = Q17F  
gen Q17_party7 = Q17G 


gen polknow1 = (Q10A==4)
gen polknow2 = (Q10B==10)
gen polknow3 = (Q10C==6)
gen polknow4 = (Q10D==8)
gen polknow5 = (Q10E==2)

alpha polknow1  polknow2 polknow3 polknow4 polknow5, detail item gen(polknow)

keep eid COUNTRY LEVEL ELECID  Q8A Q17_party* importance_* pid spid polknow AGE female
log on
* GERLowerSaxEU
fre Q8A Q17*
log off

compress
save "GERLowerSaxEU.dta", replace





*** Save variables for Bavaria at the regional level

use "SURV_GER_Bavaria_5wave_cleaned_SurveyData", clear 

gen eid = 202
gen COUNTRY = "Germany"
gen LEVEL = "Regional"
gen ELECID = "Bavaria"


fre Q6B_W1 Q7B_W1 Q46A_W1

gen  Q8A = Q7B_W1
replace Q8A = Q6B_W1 if Q8A==.
recode Q8A 80/max=.


gen importance_regional = Q34A_W1
gen importance_national = Q34B_W1
gen importance_european = Q34C_W1

fre Q46*_W1  

gen pid = Q46A_W1
gen spid = Q46B_W1
recode pid 80/max=.
recode spid 9=.



ren Q46A_W1 Q46A
ren Q46B_W1 Q46B

ren Q17A_W1 Q17_party1
ren Q17B_W1 Q17_party2
ren Q17C_W1 Q17_party3
ren Q17D_W1 Q17_party4
ren Q17E_W1 Q17_party5
ren Q17F_W1 Q17_party6


ren Q10A_W1 Q10A
ren Q10B_W1 Q10B
ren Q10C_W1 Q10C
ren Q10D_W1 Q10D
ren Q10E_W1 Q10E


gen polknow1 = (Q10A==5)
gen polknow2 = (Q10B==7)
gen polknow3 = (Q10C==3)
gen polknow4 = (Q10D==1)
*gen polknow5 = (Q10E==1) /* doe not scale */

alpha polknow1  polknow2 polknow3 polknow4, detail item gen(polknow)


keep eid COUNTRY LEVEL ELECID  Q8A Q17_party*  importance_* pid spid Q10A-Q10E age gend polknow
log on
*GERBavariaReg
fre Q8A Q17*
log off

rename age AGE

recode gend (2 = 1 ) (1 = 0) 
label define gend 1 "female" 0 "male", modify
rename gend female

egen test = rowmiss(Q10A Q10B Q10C Q10D Q17_party1 Q17_party2 Q17_party3 Q17_party4 Q17_party5 Q17_party6)
tab test
drop if test>=10
drop test

compress
save "GERBavariaReg.dta", replace





*** Save variables for Bavaria at the national level


use "SURV_GER_Bavaria_5wave_cleaned_SurveyData", clear 


gen eid = 212
gen COUNTRY = "Germany"
gen LEVEL = "National"
gen ELECID = "Bavaria"

fre Q6B_W2 Q7B_W2 Q47A_W2

gen  Q8A = Q7B_W2
replace Q8A = Q6B_W2 if Q8A==.
recode Q8A 80/max=.


gen importance_regional = Q34A_W2
gen importance_national = Q34B_W2
gen importance_european = Q34C_W2

fre Q47*_W2  

gen pid = Q47A_W2
gen spid = Q47B_W2
recode pid 1/2=1 3=2 4=3 5=4 6=5 7=6 8=7 9=8 80/max=.
recode spid 9=.


ren Q17A_W2 Q17_party1
ren Q17C_W2 Q17_party2
ren Q17D_W2 Q17_party3
ren Q17E_W2 Q17_party4
ren Q17F_W2 Q17_party5
ren Q17G_W2 Q17_party6
ren Q17H_W2 Q17_party7
ren Q17I_W2 Q17_party8

ren Q10A_W2 Q10A
ren Q10B_W2 Q10B
ren Q10C_W2 Q10C
ren Q10D_W2 Q10D
ren Q10E_W2 Q10E


gen polknow1 = (Q10A==5)
gen polknow2 = (Q10B==7)
gen polknow3 = (Q10C==9)
gen polknow4 = (Q10D==1)
gen polknow5 = (Q10E==3) 
alpha polknow1  polknow2 polknow3 polknow4 polknow5, detail item gen(polknow)

drop Q17B_W2

keep eid COUNTRY LEVEL ELECID  Q8A Q17_party*  importance_* pid spid Q10A-Q10E age gend polknow
log on
* *GERBavariaNat
fre Q8A Q17*
log off

rename age AGE

recode gend (2 = 1 ) (1 = 0) 
label define gend 1 "female" 0 "male", modify
rename gend female

egen test = rowmiss(Q10A Q10B Q10C Q10D Q17_party1 Q17_party2 Q17_party3 Q17_party4 Q17_party5 Q17_party6 Q17_party7 Q17_party8)
tab test
drop if test>10
drop test
compress
save "GERBavariaNat.dta", replace





*** Save variables for Bavaria at the european level

use "SURV_GER_Bavaria_5wave_cleaned_SurveyData", clear 

gen eid = 232
gen COUNTRY = "Germany"
gen LEVEL = "European"
gen ELECID = "Bavaria"

fre Q6A1_W4 Q8A_W4 Q47A_W4

gen  Q8A = Q8A_W4
replace Q8A = Q6A1_W4 if Q8A==.
recode Q8A 80/max=.


gen importance_regional = Q34A_W4
gen importance_national = Q34B_W4
gen importance_european = Q34C_W4

fre Q47*_W4  

gen pid = Q47A_W4
gen spid = Q47B_W4
recode pid 80/max=.
recode spid 9=.


ren Q17A_W4 Q17_party1
ren Q17B_W4 Q17_party2
ren Q17C_W4 Q17_party3
ren Q17D_W4 Q17_party4
ren Q17E_W4 Q17_party5
ren Q17F_W4 Q17_party6
ren Q17G_W4 Q17_party7
ren Q17H_W4 Q17_party8


ren Q10A_W4 Q10A
ren Q10B_W4 Q10B
ren Q10C_W4 Q10C
ren Q10D_W4 Q10D
ren Q10E_W4 Q10E


gen polknow1 = (Q10A==4)
gen polknow2 = (Q10B==10)
gen polknow3 = (Q10C==6)
gen polknow4 = (Q10D==8)
gen polknow5 = (Q10E==2) 

alpha polknow1  polknow2 polknow3 polknow4 polknow5, detail item gen(polknow)

keep eid COUNTRY LEVEL ELECID Q8A Q17_party* importance_* pid spid polknow age gend Q10*
log on
* *GERBavariaEU
fre Q8A Q17*
log close


rename age AGE

recode gend (2 = 1 ) (1 = 0) 
label define gend 1 "female" 0 "male", modify
rename gend female

egen test = rowmiss(Q10A Q10B Q10C Q10D Q10E Q17_party1 Q17_party2 Q17_party3 Q17_party4 Q17_party5 Q17_party6 Q17_party7 Q17_party8)
tab test
drop if test>10
drop test
compress
save "GERBavariaEU.dta", replace





* append datasets 
local datasets "GERLowerSaxReg GERLowerSaxNat GERLowerSaxEU GERBavariaReg GERBavariaNat"

foreach dataset in  `datasets' {
		append using `dataset'
		rm "`dataset'.dta"
	     		}


				
gen caseid = _n
* Check data consistency 0
sum caseid
return list
assert r(N)== 16206  & r(min)==1 & r(max)==16206 & r(sum)== 131325321


drop Q10*
 

ren AGE age

label define Q8A 1 "party 1" 2 "party 2" 3 "party 3" 4 "party 4" 5 "party 5" 6 "party 6" 7 "party 7" 8 "party 8" 9 "party 9", modify
fre Q8A
recode Q8A 80/max = .
fre Q8A


* Check wether coding is correct
bysort ELECID: tab eid LEVEL




*create id to merge in party information for some elections
gen id = eid*100 + Q8A

sort id caseid


* Check data consistency 1
sum caseid
return list
assert r(N)== 16206  & r(min)==1 & r(max)==16206 & r(sum)== 131325321



merge m:1 id using "parties_merge.dta"

/*
. merge m:1 id using "parties_merge.dta"

    Result                           # of obs.
    -----------------------------------------
    not matched                         3,924
        from master                     3,838  (_merge==1)
        from using                         86  (_merge==2)

    matched                            12,368  (_merge==3)
    -----------------------------------------
*/

tab _merge
keep if _merge==3

* Check data consistency 2
sum caseid
return list
assert r(N)== 12368  & r(min)==1 & r(max)== 16206 & r(sum)== 102105717



drop _merge

* Indicator for wasted vote 
gen seat_current0 = (seat_current==0)
gen seat_previous0 = (seat_previous==0)


* Check data consistency 3
sum caseid
return list
assert r(N)== 12368  & r(min)==1 & r(max)==16206 & r(sum)== 102105717
drop caseid

sum seat_current0
return list
assert r(N)== 12368  & r(min)==0 & r(max)==1 & r(sum)== 1663

sum seat_previous0
return list
assert r(N)== 12368  & r(min)==0 & r(max)==1 & r(sum)== 1105



compress
save "aggregated_GER.dta", replace






